package org.anti996.bankms.dao;

import org.anti996.bankms.model.Account;
import org.anti996.bankms.model.Card;
import org.anti996.bankms.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AdminDao {
    private static final Connection connection = DBUtil.getConnection();

    public static List<Account> getAllAccounts() {//获取用户全部卡的信息
        List<Account> accounts = new ArrayList<>();
        String sql = "SELECT * FROM account WHERE account_close is null";
        try {
            PreparedStatement Statement = connection.prepareStatement(sql);
            ResultSet set = Statement.executeQuery();
            while (set.next()) {
                Account account = new Account();
                account.setUsername(set.getString("username"));
                account.setPasswd(set.getString("passwd"));
                account.setName(set.getString("name"));
                account.setIdnumber(set.getString("idnumber"));
                account.setHometown(set.getString("hometown"));
                account.setPhone(set.getString("phone"));
                account.setAccount_open(set.getTimestamp("account_open"));
                account.setAccount_close(set.getTimestamp("account_close"));
                accounts.add(account);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return accounts;
    }

    //一、开户
    public static boolean add(Account account) {
        //1.定义SQL
        String sql = "INSERT INTO account (username,passwd,name,idnumber,hometown,phone) VALUES (?,?,?,?,?,?);";
        //open_time有默认值，不用管
        try {//2.获取预编译对象
            PreparedStatement ps = connection.prepareStatement(sql);
            //3.设置占位符———对应的model和get set还没写，到处报错XD
            ps.setString(1, account.getUsername());
            ps.setString(2, account.getPasswd());
            ps.setString(3, account.getName());
            ps.setString(4, account.getIdnumber());
            ps.setString(5, account.getHometown());
            ps.setString(6, account.getPhone());
            //ps.setTimestamp(7,account.getAccount_open());
            //ps.setTimestamp(8,account.getAccount_close());
            //4.执行
            int result = ps.executeUpdate();  // result：表中受影响的行数——>这是在做什么？
            if (result > 0) {       //确认真的改了
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    //二、销户————加一个close_time
    public static boolean delete(String username) {
        String sql = "UPDATE account SET account_close=current_timestamp WHERE username=?;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, username);

            int result = ps.executeUpdate();
            if (result > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    //二-1 设置timestamp
//    private static java.sql.Timestamp getCurrentTimeStamp() {
//        java.util.Date now = new java.util.Date();
//        return new java.sql.Timestamp(now.getTime());
//    }

    //三、办卡————1.和Admin Mainmenu对一下，办卡要输入哪些信息
    //2.需要设计一个trigger，删卡和销户之间的关系？
    public static boolean AddNewCard(Card card) {
        String sql = "INSERT INTO card (username,currency) VALUES (?,?);";//余额balance有默认值0
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, card.getUsername());
            ps.setString(2, card.getCurrency());

            int result = ps.executeUpdate();  // result：表中受影响的行数——>这是在做什么？
            if (result > 0) {       //确认真的改了
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    //四、销卡
    public static boolean DeleteCard(int id) {
        String sql = "DELETE FROM card WHERE id=?;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setInt(1, id);

            int result = ps.executeUpdate();  // result：表中受影响的行数——>这是在做什么？
            if (result > 0) {       //确认真的改了
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    //五、查询用户信息
    //输入username，输出Account记录
    public static Account selectByUsername(String username_in) {
        String sql = "SELECT * FROM account WHERE username=?;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, username_in);
            ResultSet result = ps.executeQuery();
            if (result.next()) {
                String username = result.getString("username");
                String passwd = result.getString("passwd");
                String name = result.getString("name");
                String idnumber = result.getString("idnumber");
                String hometown = result.getString("hometown");
                String phone = result.getString("phone");
                Timestamp account_open = result.getTimestamp("account_open");
                Timestamp account_close = result.getTimestamp("account_close");
                return new Account(username, passwd, name, idnumber, hometown, phone, account_open, account_close);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //六、更新用户信息————开户销户时间不能改
    public static boolean update(Account account) {
        String sql = "UPDATE account SET passwd=?,name=?,idnumber=?,hometown=?,phone=? WHERE username=?;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setString(1, account.getPasswd());
            ps.setString(2, account.getName());
            ps.setString(3, account.getIdnumber());
            ps.setString(4, account.getHometown());
            ps.setString(5, account.getPhone());
            ps.setString(6, account.getUsername());

            int result = ps.executeUpdate();  // result：表中受影响的行数
            if (result > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean updateCurrency(String cur, double rate) {
        String sql = "UPDATE exchange_rate SET rate=? WHERE currency=?";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setDouble(1, rate);
            ps.setString(2, cur);

            int result = ps.executeUpdate();  // result：表中受影响的行数
            if (result > 0) {
                UserDao.CURRENCY_NAMES = UserDao.getCurrencyNames();
                UserDao.CURRENCY_RATES = UserDao.getCurrencyRates();
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean addCurrency(String cur, String name, double rate) {
        String sql = "INSERT INTO exchange_rate(currency, rate, name) values (?,?,?)";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setString(1, cur);
            ps.setDouble(2, rate);
            ps.setString(3, name);

            int result = ps.executeUpdate();  // result：表中受影响的行数
            if (result > 0) {
                UserDao.CURRENCY_NAMES = UserDao.getCurrencyNames();
                UserDao.CURRENCY_RATES = UserDao.getCurrencyRates();
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
}
